from datetime import datetime, timedelta
from pysqlite2 import dbapi2 as sqlite
import random

# Open a connection to the database
conn = sqlite.connect("kumulusdb")

# Create a cursor to work with
cur = conn.cursor()

# Drop tables if they exist in the database 
kumulusSchema = open("config/db/kumulus-sqlite-drop.sql")
schemaStr = ""
for line in kumulusSchema.readlines():
  schemaStr += " %s" % line
cur.executescript(schemaStr)
kumulusSchema.close()

# Create the schema from scratch
kumulusSchema = open("config/db/kumulus-sqlite-schema.sql")
schemaStr = ""
for line in kumulusSchema.readlines():
  schemaStr += " %s" % line
cur.executescript(schemaStr)
kumulusSchema.close()

# Create some random data to put in the database
members = []
for i in range(1, 50):
  members.append({"name": "Member %d" % i, "password": "pass%d" % i, 
                  "first_name": "Member %d First Name" % i, "last_name": "Member %s Last Name" % i,
                  "email": "member.%d@planeur74.com" % i})

roles = ["STUDENT", "PILOT", "INSTRUCTOR", "TOWPLANE PILOT", "WINCH DRIVER"]

memberRoles = []
for member in members:
  memberRoles.append({"name": member["name"], "role": random.randint(0, 4)})

cur.executemany("INSERT INTO t_member (name, password, first_name, last_name, email) "
                "VALUES (:name, :password, :first_name, :last_name, :email)", members)
conn.commit()

cur.executemany("INSERT INTO t_member_role (name, role) VALUES (:name, :role)", memberRoles)
conn.commit()

# Some tests for selecting data from the db
cur.execute("SELECT name, password, first_name, last_name, email FROM t_member")
membersReturn = cur.fetchall()

for member in membersReturn:
  rolesStr = ""
  cur.execute("SELECT role FROM t_member_role MR WHERE MR.name = '%s'" % member[0])
  memberRolesReturn = cur.fetchall()
  for role in memberRolesReturn:
    rolesStr += "%s\t" % roles[role[0]]
  print " -- "
  print "%s \t::: %s \t::: %s\t::: %s" % (member[0], member[1], member[2], member[4])
  print "\t%s" % rolesStr

activities = []
for i in range(1, 500):
  activityMembers = random.sample(members, 5)
  for activityMember in activityMembers:
    activities.append({"day": datetime.now() + timedelta(days=i), "part_of_day": 0, 
                       "member": activityMember["name"], "role": random.randint(0, 4)})
    activities.append({"day": datetime.now() + timedelta(days=i), "part_of_day": 0, 
                       "member": activityMember["name"], "role": random.randint(0, 4)})

cur.executemany("INSERT INTO t_activity (day, part_of_day, member, role) VALUES (:day, :part_of_day, :member, :role)",
                activities)
conn.commit()

cur.execute("SELECT day, part_of_day, member, role FROM t_activity ORDER BY day ASC")
activitiesReturn = cur.fetchall()
for activity in activitiesReturn:
  print "%s \t::: %s \t::: %s" % (activity[0], activity[2], roles[activity[3]])

conn.commit()
conn.close()
